Spreadsheet programs are widely-used application programs that run on personal computers. Spreadsheet programs are commonly used for budgets, forecasting, and other finance-related tasks. In a spreadsheet program, data and formulas are entered in ledger-like forms for analysis, tracking, planning, or evaluations of the impacts of real or proposed changes on data.
Spreadsheet programs use rows and columns of cells for the display of data. Depending on the program, a single spreadsheet can contain anywhere from thousands to millions of cells. Some spreadsheet programs can also link one spreadsheet to another spreadsheet that contains related information and can update data in linked spreadsheets automatically. Spreadsheet programs may also include macro facilities, and some can be used for creating and sorting databases. For printed output, spreadsheet programs usually provide graphing capabilities and a variety of formatting options for both printed pages and text, numeric values, and captions and legends in graphs.
Each row and column in a spreadsheet is unique, so each cell is uniquely identified. Typically, each column is identified by a letter, and each row is identified by a number. Thus, the cell located at the intersection of column A and row 1 is identified as cell A1. Each cell is displayed as a rectangular space that can hold text, numerical data, or a formula that uses parameters, which may comprise data in other cells, to calculate a desired result. To ease computation, spreadsheet programs include built-in functions that perform standard calculations.
Such built-in functions may include simple arithmetic functions such as addition (designated by the + character), subtraction (designated by the--character), and multiplication (designated by the * character). Many other functions may be provided in the spreadsheet, including complex trigonometric functions to calculate the sine or cosine of a variable (designated by SIN and COS, respectively), and functions to find the natural logarithm and exponential value of a variable (designated by LN and EXP, respectively). Many other functions are provided by spreadsheet programs to perform a wide variety of calculations and data comparisons.
A formula may be constructed within a cell of the spreadsheet using one or more of the built-in functions combined with any other constants or variables. The proper syntax of each formula is entered into the spreadsheet directly by the user through the use of keystrokes on a keyboard. The various functions available in spreadsheet formulas are denoted by a particular character or characters. For example, the very simple formula A1+B1 may be entered in the spreadsheet when the user wants to add the numbers located in cells A1 and B1. More complicated formulas utilizing unfamiliar and complex functions require the user to manually enter a function identifier, such as SIN, COS, etc., to use the function. Therefore, it is clear that the use of functions in spreadsheet formulas requires that the user memorize the syntax required to correctly enter the formula. Thus, formula entry can quickly become very difficult for the user, and the probability of improperly entering such formulas is high.
Spreadsheet programs are popular among both sophisticated, expert computer users as well as ordinary, novice users. Ordinary users may include homeowners who use spreadsheet programs for maintaining household budgets and personal finances. Other ordinary users may include small business owners who use spreadsheets to perform tasks such as tracking inventory, costs, sales, etc. Frequently, these tasks require the use of complicated formulas in the spreadsheet program.
These ordinary users of computers are usually not well-versed in mathematics or computer syntax related to the formulas that can be entered in the spreadsheet. Therefore, the user may not remember the proper syntax and function identifiers required for various functions and therefore may incorrectly enter the formula or may be forced to consult a lengthy and complicated user's manual to obtain the correct syntax.
Current spreadsheet programs, such as Excel, manufactured by Microsoft Corporation of Redmond, Wash., assignee of the present invention, require that the user is familiar with the complicated syntax needed to enter formulas in the spreadsheet. Thus, when the user wishes to enter a formula in the spreadsheet, the user must manually enter the formula including all proper syntax using the keyboard without being aided or prompted by the spreadsheet program.
When a user manually enters a formula, the probability of errors in the formula are high. Common errors include improperly referencing other cells that are part of the calculation, transposing cell addresses (identifying cell A1 as cell 1A), placing multiple operators together, and mismatching parentheses. Also, incorrect spacing as well as various other typographical errors can result in an improperly entered formula.
Current spreadsheet programs utilize a "function wizard". The wizard assists the user with the entry of parameters into an individual function but does not allow the user to enter functions into the formula. Thus, the function wizard, while assisting the user with certain aspects of formula construction, only assists the user after the proper syntax for a function has been manually entered. Consequently, function wizards associated with current spreadsheet programs do not free the user from the difficult task of remembering and entering the proper formula syntax. Thus, function wizards do not assist the user in the construction of a formula in its entirety.
Also, current function wizards do not perform error correction. Although current spreadsheet programs are able to detect an improperly entered formula and generate and display a message to the user, current programs do not detect the precise error that is present within the formula and also do not provide a suggestion for the correction of the error. If the user incorrectly enters a formula, current spreadsheet programs generate a generic error message to the user indicating that the formula has been incorrectly entered. However, the generic error message neither detects nor notifies the user of the type or location of the error. Therefore, without knowing what type of error is present in the formula, a user is left with the tedious and frustrating task of poring through a lengthy and complicated formula to detect where the error exists and editing the formula to correct the error. This increases user frustration and decreases efficiency.
All of these problems associated with formula entry serve not only to make the use of formulas in spreadsheets time-consuming and inefficient but also frustrating for the user. These problems may force a frustrated user to simply give up and cease entering formulas in spreadsheet programs. By not efficiently and effectively utilizing all of the benefits that spreadsheet formulas provide, the user does not maximize the capabilities of the spreadsheet program.